import json
import sys
import sqlite3
import os
import threading

import PySide6
from PySide6 import QtWidgets, QtCore, QtGui
from PySide6.QtWidgets import QApplication, QMainWindow, QTableView, QWidget, QHeaderView, QMenu, QAbstractItemView, \
    QItemDelegate, QDialog, QMessageBox
from PySide6.QtCore import Qt, QSortFilterProxyModel, QAbstractItemModel, QMimeData, QObject, QModelIndex, \
    QRegularExpression, QTextStream, QIODevice, QThread, Signal
from PySide6.QtGui import QCursor, QGuiApplication, QDrag, QRegularExpressionValidator, QIcon

from config.VarTable import VarTable
import resources.resources_rc  # 导入图片资源不可去
from extend_ui.err_log import log_decorator


class Config(QObject):
    # 设置变量的基本属性（给用户看到用于修改）
    # 依次表示 [数据库列的数据类型,列名的中文,表格输入的限制格式类型]
    column_RW = {
        'category_id': ['TEXT', '类型号', 'category_id'],
        'type_lv1': ['TEXT', '一级类型', 'comment'],
        'type_lv2': ['TEXT', '二级类型', 'comment'],
        'var_name': ['TEXT', '变量名', 'str'],
        'var_comment': ['TEXT', '变量说明', 'comment_text'],
        'var_type': ['TEXT', '变量类型', 'var_type'],
        'var_default': ['REAL', '缺省值', 'float'],
        'var_min': ['REAL', '最小值', 'float'],
        'var_max': ['REAL', '最大值', 'float'],
        'var_unit': ['TEXT', '单位', 'unit'],
        'var_decimal': ['INTEGER', '小数位数', 'int'],
        'isWritable': ['INTEGER', '写权限', 'bool'],
        'isEEPROM': ['INTEGER', 'EEPROM', 'bool'],
        'isLogged': ['INTEGER', 'LOG', 'bool'],
        'action_define': ['TEXT', '操作定义', 'no_edit']
    }
    # 变量在程序运行过程中一些需要存储的状态
    column_RO = {
        'uniqueID': ['TEXT'],  # 由category_id+var_name组成的变量唯一id
        'addr': ['INTEGER'],  # 参数对应下位机的地址
        'var_current': ['REAL']  # 当前地址
    }

    def __init__(self, parent=None, configFile=None) -> None:
        super().__init__(parent)
        self.configFile = configFile
        self.signal_operat = parent.signal_operat
        self.queue_logging = parent.queue_logging
        self.init_configFile()
        # self.editVarTable()
        # self.genAddr()

        # 创建变量
        self.var_window = None              # 存储修改var窗口的实例
        self.ID_ADDR_before_dict = {}       # 用来存储修改前的uniqueID和addr对应关系
        self.change_data_list = []          # 用来存储修改后的uniqueID和addr对应关系

    def init_configFile(self):
        if not os.path.exists(self.configFile):
            self.conn = sqlite3.connect(self.configFile, check_same_thread=False)
            self.cur = self.conn.cursor()

            # 创建表格和主键
            self.cur.execute("CREATE TABLE var (key INTEGER PRIMARY KEY)")
            # 依次添加列
            for column in self.column_RW.keys():
                self.cur.execute("ALTER TABLE var ADD COLUMN {} {}".format(
                    column, self.column_RW[column][0]))

            for column in self.column_RO.keys():
                self.cur.execute("ALTER TABLE var ADD COLUMN {} {}".format(
                    column, self.column_RO[column][0]))

            # 创建config表格和主键
            self.cur.execute("CREATE TABLE config (name TEXT PRIMARY KEY)")
            self.cur.execute("ALTER TABLE config ADD COLUMN {} {}".format('json_str', 'TEXT'))

            # 提交
            self.conn.commit()
        else:
            self.conn = sqlite3.connect(self.configFile, check_same_thread=False)
            self.cur = self.conn.cursor()

    @log_decorator
    def editVarTable(self, Ro_flag):
        if self.var_window:
            self.var_window.setHidden(True)
            self.var_window.setHidden(False)
        else:
            self.before_open_var_table()
            # 获取修改之前的参数地址
            self.change_data_list = self.get_data_list()
            self.ID_ADDR_before_dict = self.get_ID_ADDR_dict()

            # 创建var表视图
            self.var_window = My_Var_window()
            self.table = VarTable(parent=self.var_window,
                                  configFile=self.configFile,
                                  columns=self.column_RW,
                                  Ro_flag=Ro_flag)
            self.var_window.setCentralWidget(self.table)
            self.var_window.close_signal.connect(self.var_window_close_slot)
            self.var_window.show()

    def before_open_var_table(self):
        """ 在打开 var 表之前进行的预处理动作 """
        # 将不可写的float类型数据变为NULL
        self.cur.execute('UPDATE var SET var_default=NULL, var_min=NULL, var_max=NULL WHERE isWritable = 0 or isWritable IS NULL')
        self.conn.commit()
        # 将Bool类型的NULL全部变为0
        self.cur.execute('UPDATE var SET isWritable=0 WHERE isWritable IS NULL')
        self.cur.execute('UPDATE var SET isEEPROM=0 WHERE isEEPROM IS NULL')
        self.cur.execute('UPDATE var SET isLogged=0 WHERE isLogged IS NULL')
        # 将缺省值的0全部变为空
        self.cur.execute("UPDATE var SET var_default = NULL WHERE var_default=0 or var_default='0'")
        self.conn.commit()

    def var_window_close_slot(self):
        """ 关闭var_widnow 触发的信号"""
        # 判断数据是否符合规矩
        for row in range(self.table.tableModel.rowCount()):
            for col in [1, 2, 3, 4, 6]:
                index = self.table.tableModel.index(row, col)
                data = self.table.tableModel.data(index)
                if not data:
                    QMessageBox.information(self.var_window, '提示', '前四个字段和变量类型中不允许空值存在!\n请修改后尝试关闭', QMessageBox.Yes)
                    return

        for row in range(self.table.tableModel.rowCount()):
            if self.table.tableModel.data(self.table.tableModel.index(row, 7)) != '' and self.table.tableModel.data(self.table.tableModel.index(row, 8)) != '':
                if float(self.table.tableModel.data(self.table.tableModel.index(row, 7))) < float(self.table.tableModel.data(self.table.tableModel.index(row, 8))):
                    QMessageBox.information(self.var_window, '提示', '默认值设置不能低于最小值限制\n请检查修改后重试!')
                    return
                else:
                    pass
            if self.table.tableModel.data(self.table.tableModel.index(row, 7)) != '' and self.table.tableModel.data(self.table.tableModel.index(row, 9)) != '':
                if float(self.table.tableModel.data(self.table.tableModel.index(row, 7))) > float(self.table.tableModel.data(self.table.tableModel.index(row, 9))):
                    QMessageBox.information(self.var_window, '提示', '默认值设置不能超过最大值限制\n请检查修改后重试!')
                    return
        # for row in range(self.table.tableModel.rowCount()):
        #     for col in [1, 2, 3, 4, 5, 6]:
        #         index = self.table.tableModel.index(row, col)
        #         data = self.table.tableModel.data(index)
        #         if data == 'repeat_err':
        #             QMessageBox.information(self.var_window, '提示', '有错误数据!\n请修改后尝试关闭', QMessageBox.Yes)
        #             return

        self.genAddr()

        # 创建进度条
        # self.signal_operat.emit(8, 3, [None])
        # progress = Dialog_Process(self.var_window, '排序中', 100)
        # thread = Sort_Var(self, progress)
        # thread.signal_done.connect(self.genAddr)
        # thread.start()

    def var_changed_done_slot(self):
        """ 0-no_change, 1-change data but not chnage addr, 2 chnage addr """
        if self.change_data_list == self.get_data_list():
            self.signal_operat.emit(8, 0, [None])
        elif self.ID_ADDR_before_dict == self.get_ID_ADDR_dict():
            self.signal_operat.emit(8, 1, [None])
        else:
            self.signal_operat.emit(8, 2, [None])

        # if self.change_flag:
        #     self.genAddr()
        #     if self.ID_ADDR_before_dict == self.get_ID_ADDR_dict():
        #         self.signal_operat.emit(8, 1, [None])
        #     else:
        #         self.signal_operat.emit(8, 2, [None])
        # else:
        #     self.signal_operat.emit(8, 0, [None])

    """ 此函数为给表格排序的函数，为了解决卡顿问题转为线程，函数暂时保留 """
    # def sortVar(self):
    #     """ 将修改后的表格重新排序 """
    #     # 创建临时表格
    #     columns = tuple(self.column_RW.keys()) + tuple(self.column_RO.keys())
    #     self.cur.execute("CREATE TABLE var2 (key INTEGER PRIMARY KEY)")
    #     # 依次添加列
    #     for column in self.column_RW.keys():
    #         self.cur.execute("ALTER TABLE var2 ADD COLUMN {} {}".format(
    #             column, self.column_RW[column][0]))
    #
    #     for column in self.column_RO.keys():
    #         self.cur.execute("ALTER TABLE var2 ADD COLUMN {} {}".format(
    #             column, self.column_RO[column][0]))
    #
    #     self.cur.execute("""SELECT {0} FROM var order by category_id ASC,
    #                         case WHEN var_type='enum' THEN 0
    #                         WHEN var_type='bit' THEN 1
    #                         WHEN var_type='int' THEN 2
    #                         WHEN var_type='float' THEN 3 end  ASC,
    #                         CAST(rtrim(var_name, '0123456789-_ ') AS INTEGER) ASC,
    #                         CAST(ltrim(var_name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_ ') AS INTEGER) ASC
    #                         """.format(str(columns).replace('(', '').replace(')', '').replace("'", '')))
    #     results = self.cur.fetchall()
    #     for result in results:
    #         self.cur.execute("INSERT INTO var2 {0} VALUES {1}".format(str(columns).replace("'", ''), str(result).replace('None', 'Null')))
    #     self.conn.commit()
    #     self.cur.execute("DROP TABLE var")
    #     self.conn.commit()
    #     self.cur.execute("ALTER TABLE var2 RENAME TO var")
    #     self.conn.commit()
    #     print('sort done')

    def genAddr(self):
        """ 查询e2prom个数 """
        # self.cur.execute("SELECT COUNT(*) FROM var WHERE isEEPROM = 1")
        # self.eepromNum = self.cur.fetchone()[0]

        """ 如果 var_comment 为空 就把 var_name 填充进去 """
        self.cur.execute("UPDATE var SET var_comment = var_name WHERE var_comment IS NULL OR var_comment = '' ")
        self.conn.commit()

        """ 将序列按照 id 和 key 排序 """
        # 按 id 和 key 排序查询表格数据，获取排序后的索引
        self.cur.execute("SELECT rowid FROM var ORDER BY category_id ASC , key ASC")
        rows = self.cur.fetchall()
        sorted_indexes = [row[0] for row in rows]
        # 更新旧表格中的 key 值为排序后的索引
        for index, rowid in enumerate(sorted_indexes):
            self.cur.execute("UPDATE var SET key = ? WHERE rowid = ?", (index + 1, rowid))
        self.conn.commit()

        """ 将按照 isEEPROM 排序 然后生成 addr 和 uniqueID """
        self.cur.execute(
            "SELECT key,category_id,var_name FROM var ORDER BY case WHEN isEEPROM=1 THEN 1 else 0 end DESC,case WHEN isWritable=1 THEN 1 else 0 end DESC,key ASC"
        )
        results = self.cur.fetchall()
        for i, result in enumerate(results):
            self.cur.execute("UPDATE var SET addr=?,uniqueID=? WHERE key=?",
                             (i, result[1] + '_' + result[2], result[0]))
        self.conn.commit()

        """ 处理 特殊数据， 给数据做转型 """
        # 将所有bool类型的字段NULL或是'' 变为 0
        self.cur.execute("UPDATE var SET isEEPROM = 0 WHERE isEEPROM IS NULL OR isEEPROM = ''")
        self.cur.execute("UPDATE var SET isWritable = 0 WHERE isWritable IS NULL OR isWritable = ''")
        self.cur.execute("UPDATE var SET isLogged = 0 WHERE isLogged IS NULL OR isLogged = ''")
        # 将所有当前值赋初值0
        self.cur.execute('UPDATE var SET var_current = 0')
        # 将单位为NULL的变为'' 方便与字符串拼接
        self.cur.execute("UPDATE var SET var_unit='' WHERE var_unit IS NULL ")
        self.conn.commit()

        """ 将 key 重新排序 并赋值 * 65536 """
        # 将所有key重新排序并赋值*65536
        self.cur.execute(
                         "UPDATE var SET key = new_key FROM (SELECT rowid AS id, ROW_NUMBER() OVER (ORDER BY key) AS new_key FROM var ORDER BY category_id ASC , key ASC ) AS subquery WHERE var.rowid = subquery.id;"
                         )
        # 分开写防止KEY重复
        self.cur.execute(
                         "UPDATE var SET key = key * 65536;"
                         )
        self.conn.commit()

        self.var_changed_done_slot()
        print('done')

    def get_data_list(self):
        """ 获取修改前后的数据库所有数据 """
        data_list = []
        columns = tuple(self.column_RW.keys()) + tuple(self.column_RO.keys())
        self.cur.execute('''SELECT {0} FROM var'''.format(str(columns).replace('(', '').replace(')', '').replace("'", '')))
        # self.cur.execute("SELECT * FROM var")
        results = self.cur.fetchall()
        for result in results:
            data_list.append(result)
        return data_list

    def get_ID_ADDR_dict(self):
        """ 获取修改前后的 uniqueID和addr 的对应字典判断是否需要重新编译下位机的头文件 """
        value_dict = {}
        self.cur.execute("SELECT uniqueID, addr FROM var")
        results = self.cur.fetchall()
        for uniqueID, addr in results:
            value_dict[uniqueID] = addr
        return value_dict


class Sort_Var(QThread):
    """
        给var表格排序的线程
    """
    signal_done = Signal()

    def __init__(self, parent=None, progress=None):
        super(Sort_Var, self).__init__(parent)
        # 获取变量
        self.column_RW = parent.column_RW
        self.column_RO = parent.column_RO
        self.configFile = parent.configFile
        self.progress = progress
        self.conn = sqlite3.connect(self.configFile, check_same_thread=False)
        self.cur = self.conn.cursor()

    def run(self):
        """ 将修改后的表格重新排序 """

        # 创建临时表格
        columns = tuple(self.column_RW.keys()) + tuple(self.column_RO.keys())
        # 判断是否有var2的数据库，有就删掉没有就跳过
        self.cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='var2'")
        result = self.cur.fetchone()
        if result:
            self.cur.execute("DROP TABLE var2")
            self.conn.commit()
        self.progress.signal_update.emit(10)
        self.cur.execute("CREATE TABLE var2 (key INTEGER PRIMARY KEY)")
        self.progress.signal_update.emit(10)
        # 依次添加列
        for column in self.column_RW.keys():
            self.cur.execute("ALTER TABLE var2 ADD COLUMN {} {}".format(
                column, self.column_RW[column][0]))

        for column in self.column_RO.keys():
            self.cur.execute("ALTER TABLE var2 ADD COLUMN {} {}".format(
                column, self.column_RO[column][0]))
        self.progress.signal_update.emit(10)

        # 方式一
        self.cur.execute("""SELECT {0} FROM var order by category_id ASC,
                            case WHEN var_type='enum' THEN 0
                            WHEN var_type='bit' THEN 1
                            WHEN var_type='int' THEN 2
                            WHEN var_type='float' THEN 3 end  ASC,
                            rtrim(var_name, '0123456789-_ ') ASC,
                            CAST(ltrim(var_name, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_ ') AS INTEGER) ASC
                            """.format(str(columns).replace('(', '').replace(')', '').replace("'", '')))

        # 方式二
        # self.cur.execute("""SELECT {0} FROM var order by category_id ASC,
        #                     case WHEN var_type='enum' THEN 0
        #                     WHEN var_type='bit' THEN 1
        #                     WHEN var_type='int' THEN 2
        #                     WHEN var_type='float' THEN 3 end  ASC,
        #                     LENGTH(var_name) - LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(var_name, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')) ASC,
        #                     var_name ASC
        #                     """.format(str(columns).replace('(', '').replace(')', '').replace("'", '')))

        results = self.cur.fetchall()
        self.progress.signal_update.emit(10)
        for result in results:
            self.cur.execute("INSERT INTO var2 {0} VALUES {1}".format(str(columns).replace("'", ''), str(result).replace('None', 'Null')))
            self.progress.signal_update.emit(40 / len(results))
        self.conn.commit()
        self.cur.execute("DROP TABLE var")
        self.conn.commit()
        self.progress.signal_update.emit(10)
        self.cur.execute("ALTER TABLE var2 RENAME TO var")
        self.conn.commit()
        self.progress.signal_update.emit(10)
        self.signal_done.emit()
        print('sort done')


class My_Var_window(QMainWindow):
    close_signal = QtCore.Signal()

    def __init__(self, parent=None):
        super(My_Var_window, self).__init__(parent)
        self.signal = QtCore.Signal()
        self.setWindowIcon(QIcon(':/logo.ico'))
        self.setWindowTitle('var表')


    def closeEvent(self, event: PySide6.QtGui.QCloseEvent) -> None:
        self.close_signal.emit()
        # super(My_Var_window, self).closeEvent(event)
        event.ignore()

if __name__ == '__main__':
    app = QApplication([])
    window = QMainWindow()
    config = Config(window, configFile='config.db')
    window.setCentralWidget(config.table)
    window.show()
    app.exec()
